---中间结果
select user_id, click_time, 
    if(
        lag(unix_timestamp(click_time),1,-1800) over(
            partition by user_id order by unix_timestamp(click_time)) 
        - unix_timestamp(click_time) <= -1800,1,0) grp --时间差
from user_clicklog
order by user_id, click_time;


---解答
with tmp as (
select user_id, click_time, 
    unix_timestamp(click_time) c1,
    if(
        lag(unix_timestamp(click_time),1,-1800) over(
            partition by user_id order by unix_timestamp(click_time)) 
        - unix_timestamp(click_time) <= -1800,1,0) grp --时间差用户名检测
from user_clicklog
order by user_id, click_time
),
tmp2 as (
select user_id, click_time, c1, grp,
    sum(grp) over(order by user_id, c1) sumgrp --时间差用户名分组
from tmp
)
select user_id, click_time, grp, sumgrp,
    row_number() over(partition by sumgrp
                      order by user_id, c1) result
from tmp2
order by user_id, click_time;